﻿#region Using Region

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;

#endregion

namespace ICE.Vacations.DataBase
{
    public class SQLDataClass
    {
        private SqlConnection sqlConnection { get; set; }

        public void OpenConnection()
        {
            try
            {
             
              sqlConnection = new SqlConnection("Data Source=CRSJO-AGUILARR\\SQLEXPRESS;Database=VACATIONS;User Id=sa;Password=$Unisys2k10;");

                sqlConnection.Open();

            }
            catch (SqlException sqlExc)
            {
                throw (sqlExc);
            }
        }


        public void CloseConnection()
        {
            try
            {
                sqlConnection.Close();
                sqlConnection.Dispose();
            }
            catch (SqlException sqlExc)
            {
                throw (sqlExc);
            }
        }


        public int SQLUpdate(string objectID, string version, string historico, bool openConnection)
        {
            try
            {
                if (openConnection)
                    this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                //sqlCommand.CommandTimeout = Convert.ToInt32(Configuration.topQuery) * (60000);

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //sqlCommand.CommandText = Configuration.updateCommand;


                SqlParameter objID = sqlCommand.Parameters.Add("@sOBJECT_ID", SqlDbType.VarChar);
                objID.Direction = ParameterDirection.Input;
                objID.Value = objectID;

                SqlParameter VERSION_WORKITEM = sqlCommand.Parameters.Add("@sVersion_Workitem", SqlDbType.VarChar);
                VERSION_WORKITEM.Direction = ParameterDirection.Input;
                VERSION_WORKITEM.Value = version;

                SqlParameter HISTORICO = sqlCommand.Parameters.Add("@sHistorico", SqlDbType.VarChar);
                HISTORICO.Direction = ParameterDirection.Input;
                HISTORICO.Value = historico;

                int result = sqlCommand.ExecuteNonQuery();
                if (openConnection)
                    this.CloseConnection();

                return result;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }

        public int SQLInsert(string idSolicitud,string usuario,string empleado,int tDiasDisponibles,
            int tDiasSolicitados,string fechaInicio, string fechaFin, string fechaRegistro, string fechaWFL, string estado,bool openConnection)
        {
            try
            {
                if (openConnection)
                    this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "INSERTAR_SOLICITUD";


                SqlParameter objID = sqlCommand.Parameters.Add("@sIDSOLICITUD", SqlDbType.VarChar);
                objID.Direction = ParameterDirection.Input;
                objID.Value = idSolicitud;

                SqlParameter USUARIO = sqlCommand.Parameters.Add("@sUSUARIO", SqlDbType.VarChar);
                USUARIO.Direction = ParameterDirection.Input;
                USUARIO.Value = usuario;

                SqlParameter EMPLEADO = sqlCommand.Parameters.Add("@sEMPLEADO", SqlDbType.VarChar);
                EMPLEADO.Direction = ParameterDirection.Input;
                EMPLEADO.Value = empleado;

                SqlParameter TDIASDISPONIBLES = sqlCommand.Parameters.Add("@sTDIASDISPONIBLES", SqlDbType.Int);
                TDIASDISPONIBLES.Direction = ParameterDirection.Input;
                TDIASDISPONIBLES.Value = tDiasDisponibles;

                SqlParameter TDIASSOLICITADOS = sqlCommand.Parameters.Add("@sTDIASSOLICITADOS", SqlDbType.Int);
                TDIASSOLICITADOS.Direction = ParameterDirection.Input;
                TDIASSOLICITADOS.Value = tDiasSolicitados;

                SqlParameter FECHA_INICIO = sqlCommand.Parameters.Add("@sFECHA_INICIO", SqlDbType.VarChar);
                FECHA_INICIO.Direction = ParameterDirection.Input;
                FECHA_INICIO.Value = fechaInicio;

                SqlParameter FECHA_FIN = sqlCommand.Parameters.Add("@sFECHA_FIN", SqlDbType.VarChar);
                FECHA_FIN.Direction = ParameterDirection.Input;
                FECHA_FIN.Value = fechaFin;

                SqlParameter FECHA_REGISTRO = sqlCommand.Parameters.Add("@sFECHA_REGISTRO", SqlDbType.VarChar);
                FECHA_REGISTRO.Direction = ParameterDirection.Input;
                FECHA_REGISTRO.Value = fechaRegistro;

                SqlParameter FECHA_ENVIO_WF = sqlCommand.Parameters.Add("@sFECHA_ENVIO_WF", SqlDbType.VarChar);
                FECHA_ENVIO_WF.Direction = ParameterDirection.Input;
                FECHA_ENVIO_WF.Value = fechaWFL;

                SqlParameter ESTADO_SOLICITUD = sqlCommand.Parameters.Add("@sESTADO_SOLICITUD", SqlDbType.VarChar);
                ESTADO_SOLICITUD.Direction = ParameterDirection.Input;
                ESTADO_SOLICITUD.Value = estado;

                int result = sqlCommand.ExecuteNonQuery();
                if (openConnection)
                    this.CloseConnection();

                return result;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }

        public SqlDataReader SQLRead(string query)
        {
            try
            {
               // this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "LEER_RECURSO";


                SqlParameter parameter = sqlCommand.Parameters.Add("@sUSUARIO", SqlDbType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = query;

                return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure               
            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }
    }
}
